package psrc.bd.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import psrc.logic.util.beans.RepresentanteBean;
import psrc.logic.util.beans.TipoRepresentante;

public class RepresentanteDAO extends AbstractDAO {

	public RepresentanteDAO(Connection conn) {
		super(conn);
	}

	@Override
	public <T> void adiciona(T _bean) throws SQLException {
		RepresentanteBean bean = (RepresentanteBean) _bean;
		String sql = "insert into Representante "
				+ "(nome, banco, agencia, conta_corrente, telefone_1, telefone_2, email, tipo_representante) values (?, ?, ?, ?, ?, ?, ?, CAST(? AS tipo_representante))";
		try {
			PreparedStatement stmt = connection.prepareStatement(sql);

			stmt.setString(1, bean.getNome());
			stmt.setString(2, bean.getBanco());
			stmt.setString(3, bean.getAgencia());
			stmt.setString(4, bean.getContaCorrente());
			stmt.setString(5, bean.getTelefone1());
			stmt.setString(6, bean.getTelefone2());
			stmt.setString(7, bean.getEmail());
			stmt.setString(8, bean.getTipoRepresentante().toString());
			//stmt.setInt(9, bean.getUsuario());
			
			stmt.execute();
			stmt.close();

		} catch (SQLException e) {
			throw e;
		}
	}

	@Override
	public <T> void remove(T _bean) throws SQLException{
		RepresentanteBean bean = (RepresentanteBean) _bean;
		try {
			PreparedStatement stmt = connection.prepareStatement("delete from Representante where id_representante=?");

			stmt.setInt(1, Integer.parseInt(bean.getIdRepresentante()));

			stmt.execute();
			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
	}

	@Override
	public <T> void edita(T _bean) throws SQLException{
		RepresentanteBean bean = (RepresentanteBean) _bean;

		String sql = "update Representante set " +"nome=?, banco=?, agencia=?, conta_corrente=?, telefone_1=?, telefone_2=?, email=?, tipo_representante=CAST(? as tipo_representante), usuario=? where representante=?";
		try {
			PreparedStatement stmt = connection.prepareStatement(sql);

			stmt.setString(1, bean.getNome());
			stmt.setString(2, bean.getBanco());
			stmt.setString(3, bean.getAgencia());
			stmt.setString(4, bean.getContaCorrente());
			stmt.setString(5, bean.getTelefone1());
			stmt.setString(6, bean.getTelefone2());
			stmt.setString(7, bean.getEmail());
			stmt.setString(8, bean.getTipoRepresentante().toString());
			stmt.setInt(9, bean.getUsuario());
			stmt.setInt(10, Integer.parseInt(bean.getIdRepresentante()));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
	}

	@Override
	public <T> List<T> seleciona() throws SQLException {
		List<RepresentanteBean> representantes = new ArrayList<RepresentanteBean>();

		try {
			PreparedStatement stmt = connection.prepareStatement("select * from Representante");

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				RepresentanteBean bean = new RepresentanteBean();
				bean.setNome(rs.getString("nome"));
				bean.setBanco(rs.getString("banco"));
				bean.setAgencia(rs.getString("agencia"));
				bean.setContaCorrente(rs.getString("conta_corrente"));
				bean.setTelefone1(rs.getString("telefone_1"));
				bean.setTelefone2(rs.getString("telefone_2"));
				bean.setEmail(rs.getString("email"));
				bean.setTipoRepresentante(TipoRepresentante.valueOf(rs.getString("tipo_representante")));
				bean.setUsuario(rs.getInt("usuario"));
				bean.setIdRepresentante(rs.getString("id_representante"));

				representantes.add(bean);
			}
			rs.close();
			stmt.close();
			return (List<T>) representantes;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
	}

@Override
	public <T> List<T> selecionaPorId(T param) throws SQLException {
		List<RepresentanteBean> representantes = new ArrayList<RepresentanteBean>();
		RepresentanteBean user = (RepresentanteBean) param;
		try {
			PreparedStatement stmt = connection
					.prepareStatement("select * from Representante where id_representante=?");
			stmt.setString(1, user.getIdRepresentante());
			ResultSet rs = stmt.executeQuery();
			
			while (rs.next()) {
				RepresentanteBean bean = new RepresentanteBean();
				bean.setNome(rs.getString("nome"));
				bean.setBanco(rs.getString("banco"));
				bean.setAgencia(rs.getString("agencia"));
				bean.setContaCorrente(rs.getString("conta_corrente"));
				bean.setTelefone1(rs.getString("telefone_1"));
				bean.setTelefone2(rs.getString("telefone_2"));
				bean.setEmail(rs.getString("email"));
				bean.setTipoRepresentante(TipoRepresentante.valueOf(rs.getString("tipo_representante")));
				bean.setUsuario(rs.getInt("usuario"));
				bean.setIdRepresentante(rs.getString("id_representante"));

				representantes.add(bean);
			}
			rs.close();
			stmt.close();
			return (List<T>) representantes;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
	}
}
